'use strict';

const Service = require('egg').Service;

/**
 * 人员服务类
 * 处理人员相关的数据库操作和业务逻辑
 */
class PersonService extends Service {
  /**
   * 获取人员列表
   * 支持按姓名和部门筛选，支持分页查询
   * @param {Object} params - 查询参数
   * @param {string} [params.person_name] - 人员姓名，模糊匹配
   * @param {number} [params.department_id] - 部门ID
   * @param {number} [params.page=1] - 页码
   * @param {number} [params.page_size=10] - 每页条数
   * @returns {Promise<Object>} 包含列表和分页信息的对象
   */
  async findAll(params) {
    const {
      person_name,
      department_id,
      page = 1,
      page_size = 10
    } = params;

    // 构建 WHERE 条件
    let conditions = ['p.is_deleted = 0'];
    const queryParams = [];

    if (person_name) {
      conditions.push('p.person_name LIKE ?');
      queryParams.push(`%${person_name}%`);
    }

    if (department_id) {
      conditions.push('p.department_id = ?');
      queryParams.push(department_id);
    }

    // 计算总数
    const countSql = `SELECT COUNT(*) as total FROM tb_person p WHERE ${conditions.join(' AND ')}`;
    const totalResult = await this.app.mysql.query(countSql, queryParams);
    const total = totalResult[0].total;

    // 查询数据
    const offset = (page - 1) * page_size;
    const sql = `
      SELECT p.*, d.department_name 
      FROM tb_person p
      LEFT JOIN tb_department d ON p.department_id = d.department_id AND d.is_deleted = 0
      WHERE ${conditions.join(' AND ')}
      ORDER BY p.person_id ASC
      LIMIT ? OFFSET ?
    `;

    const result = await this.app.mysql.query(sql, [...queryParams, page_size, offset]);

    return {
      list: result,
      pagination: {
        total,
        page: parseInt(page),
        page_size: parseInt(page_size),
        total_pages: Math.ceil(total / page_size)
      }
    };
  }

  /**
   * 根据ID获取人员信息
   * @param {number} id - 人员ID
   * @returns {Promise<Object|null>} 人员信息，不存在时返回null
   */
  async findById(id) {
    const result = await this.app.mysql.query(
      'SELECT * FROM tb_person WHERE person_id = ? AND is_deleted = 0',
      [id]
    );
    return result[0];
  }

  /**
   * 创建新人员
   * @param {Object} data - 人员数据
   * @param {string} data.person_name - 人员姓名
   * @param {number} data.department_id - 部门ID
   * @param {string} data.password - 密码
   * @param {string} [data.phone_number] - 手机号
   * @param {string} [data.role_main='普通用户'] - 角色
   * @param {string} [data.role='普通用户'] - 角色
   * @param {string} [data.role_destination='普通用户'] - 角色
   * @param {string} [data.role_ov='普通用户'] - 角色
   * @returns {Promise<Object>} 创建的人员信息
   */
  async create(data) {
    const result = await this.app.mysql.insert('tb_person', {
      person_name: data.person_name,
      position: data.position,
      department_id: data.department_id,
      password: data.password,
      phone_number: data.phone_number,
      role_main: data.role_mian || '普通用户',
      role: data.role || '普通用户',
      role_destination: data.role_destination || '普通用户',
      role_ov: data.role_ov || '普通用户',
      is_actived: 0,
      is_deleted: 0,
    });

    return await this.findById(result.insertId);
  }

  /**
   * 更新人员信息
   * @param {number} id - 人员ID
   * @param {Object} data - 更新数据
   * @param {string} [data.person_name] - 人员姓名
   * @param {number} [data.position] - 职位
   * @param {number} [data.department_id] - 部门ID
   * @param {string} [data.phone_number] - 手机号
   * @param {string} [data.role_main] - 角色
   * @param {string} [data.role] - 角色
   * @param {string} [data.role_destination] - 角色
   * @param {string} [data.role_ov] - 角色
   * @returns {Promise<Object|null>} 更新后的人员信息，不存在时返回null
   */
  async update(id, data) {
    // 先检查记录是否存在且未删除
    const person = await this.findById(id);
    if (!person) {
      return null;
    }

    // 执行更新
    await this.app.mysql.update('tb_person', {
      person_name: data.person_name,
      position: data.position,
      department_id: data.department_id,
      // password: data.password,
      phone_number: data.phone_number,
      role_main: data.role_main,
      role: data.role,
      role_destination: data.role_destination,
      role_ov: data.role_ov,
    }, {
      where: {
        person_id: id,
        is_deleted: 0
      }
    });

    return await this.findById(id);
  }

  /**
   * 更新人员信息
   * @param {number} id - 人员ID
   * @param {Object} data - 更新数据
   * @param {string} [data.is_actived] - 人员姓名
   * @returns {Promise<Object|null>} 更新后的人员信息，不存在时返回null
   */
  async updateActived(id, data) {
    // 先检查记录是否存在且未删除
    const person = await this.findById(id);
    if (!person) {
      return null;
    }

    // 执行更新
    await this.app.mysql.update('tb_person', {
      is_actived: data.is_actived
    }, {
      where: {
        person_id: id,
        is_deleted: 0
      }
    });

    return await this.findById(id);
  }

  /**
   * 重置密码
   * @param {number} id - 人员ID
   * @returns {Promise<Object|null>} 更新后的人员信息，不存在时返回null
   */
  async resetPassword(id) {
    // 先检查记录是否存在且未删除
    const person = await this.findById(id);
    if (!person) {
      return null;
    }

    // 执行更新
    await this.app.mysql.update('tb_person', {
      password: 'SllSbXRAMjAyNENlbnRlUiE='
    }, {
      where: {
        person_id: id,
        is_deleted: 0
      }
    });

    return await this.findById(id);
  }

  /**
   * 软删除人员
   * @param {number} id - 人员ID
   * @returns {Promise<Object|null>} 删除的人员信息，不存在时返回null
   */
  async delete(id) {
    // 先检查记录是否存在且未删除
    const person = await this.findById(id);
    if (!person) {
      return null;
    }

    // 执行软删除
    await this.app.mysql.update('tb_person', {
      is_deleted: 1,
    }, {
      where: {
        person_id: id,
        is_deleted: 0
      }
    });

    return person;
  }

  /**
   * 根据手机号和密码查询用户
   * @param {string} phoneNumber - 手机号
   * @param {string} password - 密码
   * @returns {Promise<Object|null>} 用户信息，不存在时返回null
   */
  async findByPhoneAndPassword(phoneNumber, password) {
    const result = await this.app.mysql.query(
      'SELECT * FROM tb_person WHERE phone_number = ? AND password = ? AND is_deleted = 0 AND is_actived = 1',
      [phoneNumber, password]
    );
    return result[0];
  }

  /**
   * 获取所有人员的投稿统计信息
   * 支持多维度筛选和排序
   * @param {Object} params - 查询参数
   * @param {string} [params.order_by='score'] - 排序方式：score-按分数，count-按数量
   * @param {string} [params.start_date] - 开始日期，格式：YYYY-MM-DD
   * @param {string} [params.end_date] - 结束日期，格式：YYYY-MM-DD
   * @param {number} [params.project_group_id] - 项目组ID
   * @param {number} [params.department_id] - 部门ID
   * @param {string} [params.person_name] - 人员姓名，模糊匹配
   * @param {number} [params.approved_status] - 审核状态
   * @param {0|1|2} [params.has_platform_submit_date] - 平台提交日期筛选（0:所有, 1:有值, 2:无值）
   * @returns {Promise<Array>} 统计结果列表
   */
  async getAllPersonContributionStats(params) {
    const {
      order_by = 'score',
        start_date,
        end_date,
        project_group_id,
        department_id,
        person_name,
        approved_status,
        has_platform_submit_date // 新增筛选参数
    } = params;

    // 构建贡献表的筛选条件
    let contributionConditions = ['c.isDeleted = 0'];
    const queryParams = [];

    if (start_date) {
      contributionConditions.push('c.created_at >= ?');
      queryParams.push(`${start_date} 00:00:00`);
    }

    if (end_date) {
      contributionConditions.push('c.created_at <= ?');
      queryParams.push(`${end_date} 23:59:59`);
    }

    if (project_group_id) {
      contributionConditions.push('c.project_group_id = ?');
      queryParams.push(project_group_id);
    }

    if (department_id) {
      contributionConditions.push(`(
      c.author1_department_id = ? OR 
      c.author2_department_id = ? OR 
      c.author3_department_id = ?
    )`);
      queryParams.push(department_id, department_id, department_id);
    }

    // 添加审核状态条件
    if (approved_status !== undefined) {
      contributionConditions.push('c.approved_status = ?');
      queryParams.push(approved_status);
    }

    // 新增：平台提交日期筛选（适配 TIMESTAMP 类型，仅用 IS NULL/IS NOT NULL 判断）
    if (has_platform_submit_date === 1) {
      // 1: 筛选有值的记录（TIMESTAMP 不为 NULL）
      contributionConditions.push('c.platform_submit_date IS NOT NULL');
    } else if (has_platform_submit_date === 2) {
      // 2: 筛选无值的记录（TIMESTAMP 为 NULL）
      contributionConditions.push('c.platform_submit_date IS NULL');
    }
    // 0 或未传参时不添加条件（查询所有）

    // 构建人员表的筛选条件
    let personConditions = ['p.is_deleted = 0'];
    if (person_name) {
      personConditions.push('p.person_name LIKE ?');
      queryParams.push(`%${person_name}%`);
    }

    const sql = `
    SELECT 
      person_stats.person_id as person_id,
      person_stats.person_name as person_name,
      person_stats.department_id as department_id,
      person_stats.department_name as department_name,
      person_stats.author1_count as author1_count,
      person_stats.author1_total_score as author1_total_score,
      person_stats.author2_count as author2_count,
      person_stats.author2_total_score as author2_total_score,
      person_stats.author3_count as author3_count,
      person_stats.author3_total_score as author3_total_score,
      (person_stats.author1_count + person_stats.author2_count + person_stats.author3_count) as total_count,
      (IFNULL(person_stats.author1_total_score, 0) + IFNULL(person_stats.author2_total_score, 0) + IFNULL(person_stats.author3_total_score, 0)) as total_score
    FROM 
      (
        SELECT 
          p.person_id,
          p.person_name,
          p.department_id,
          d.department_name,
          -- 作者1统计
          SUM(CASE WHEN c.author1 = p.person_id THEN 1 ELSE 0 END) as author1_count,
          SUM(CASE WHEN c.author1 = p.person_id THEN c.author1_score ELSE 0 END) as author1_total_score,
          
          -- 作者2统计
          SUM(CASE WHEN c.author2 = p.person_id THEN 1 ELSE 0 END) as author2_count,
          SUM(CASE WHEN c.author2 = p.person_id THEN c.author2_score ELSE 0 END) as author2_total_score,
          
          -- 作者3统计
          SUM(CASE WHEN c.author3 = p.person_id THEN 1 ELSE 0 END) as author3_count,
          SUM(CASE WHEN c.author3 = p.person_id THEN c.author3_score ELSE 0 END) as author3_total_score
        FROM 
          tb_person p
          LEFT JOIN tb_department d ON p.department_id = d.department_id
          LEFT JOIN tb_contribution c ON 
            (c.author1 = p.person_id OR c.author2 = p.person_id OR c.author3 = p.person_id)
            AND ${contributionConditions.join(' AND ')}
        WHERE 
          ${personConditions.join(' AND ')}
        GROUP BY 
          p.person_id, p.person_name, p.department_id, d.department_name
      ) as person_stats
      HAVING 
        total_count > 0  -- 只显示有投稿的人员
      ORDER BY 
        ${order_by === 'count' ? 'total_count' : 'total_score'} DESC,
        ${order_by === 'count' ? 'total_score' : 'total_count'} DESC,
        person_stats.person_id ASC
  `;

    const result = await this.app.mysql.query(sql, queryParams);

    // 处理数据，格式化分数
    return result.map(item => ({
      person_id: item.person_id,
      person_name: item.person_name,
      department_id: item.department_id,
      department_name: item.department_name,
      author1: {
        count: item.author1_count || 0,
        score: Number(item.author1_total_score || 0).toFixed(2)
      },
      author2: {
        count: item.author2_count || 0,
        score: Number(item.author2_total_score || 0).toFixed(2)
      },
      author3: {
        count: item.author3_count || 0,
        score: Number(item.author3_total_score || 0).toFixed(2)
      },
      total: {
        count: item.total_count || 0,
        score: Number(item.total_score || 0).toFixed(2)
      }
    }));
  }

  /**
   * 获取单个人员的投稿统计信息
   * @param {number} personId - 人员ID
   * @returns {Promise<Object>} 统计结果
   */
  async getContributionStats(personId) {
    const sql = `
      SELECT 
        -- 作者1统计
        SUM(CASE WHEN author1 = ? THEN 1 ELSE 0 END) as author1_count,
        SUM(CASE WHEN author1 = ? THEN author1_score ELSE 0 END) as author1_total_score,
        
        -- 作者2统计
        SUM(CASE WHEN author2 = ? THEN 1 ELSE 0 END) as author2_count,
        SUM(CASE WHEN author2 = ? THEN author2_score ELSE 0 END) as author2_total_score,
        
        -- 作者3统计
        SUM(CASE WHEN author3 = ? THEN 1 ELSE 0 END) as author3_count,
        SUM(CASE WHEN author3 = ? THEN author3_score ELSE 0 END) as author3_total_score
      FROM 
        tb_contribution
      WHERE 
        isDeleted = 0 
        AND approved_status = 1  -- 只统计审核通过的
        AND (author1 = ? OR author2 = ? OR author3 = ?)  -- 作为任意作者的投稿
    `;

    const result = await this.app.mysql.query(sql, [
      personId, personId, // 作者1
      personId, personId, // 作者2
      personId, personId, // 作者3
      personId, personId, personId // WHERE条件
    ]);

    // 处理 null 值转换为数字
    const stats = result[0];
    return {
      author1: {
        count: stats.author1_count || 0,
        score: Number(stats.author1_total_score || 0).toFixed(2)
      },
      author2: {
        count: stats.author2_count || 0,
        score: Number(stats.author2_total_score || 0).toFixed(2)
      },
      author3: {
        count: stats.author3_count || 0,
        score: Number(stats.author3_total_score || 0).toFixed(2)
      },
      total: {
        count: (stats.author1_count || 0) + (stats.author2_count || 0) + (stats.author3_count || 0),
        score: Number((stats.author1_total_score || 0) + (stats.author2_total_score || 0) + (stats.author3_total_score || 0)).toFixed(2)
      }
    };
  }

  /**
   * 修改密码
   * 验证旧密码正确后才允许修改
   * @param {number} personId - 人员ID
   * @param {string} oldPassword - 旧密码
   * @param {string} newPassword - 新密码
   * @returns {Promise<Object|{error: string}|null>} 修改结果，不存在返回null，密码错误返回错误信息
   */
  async changePassword(personId, oldPassword, newPassword) {
    // 先检查人员是否存在
    const person = await this.findById(personId);
    if (!person) {
      return null;
    }

    // 验证旧密码是否正确
    if (person.password !== oldPassword) {
      return {
        error: 'wrong_password'
      };
    }

    // 更新密码
    await this.app.mysql.update('tb_person', {
      password: newPassword,
    }, {
      where: {
        person_id: personId,
        is_deleted: 0
      }
    });

    return {
      success: true
    };
  }

  /**
   * 获取所有人员列表
   * 支持按姓名筛选，无需分页
   * @param {Object} params - 查询参数
   * @param {string} [params.person_name] - 人员姓名，模糊匹配
   * @returns {Promise<Array>} 人员列表
   */
  async findAllWithoutPaging(params = {}) {
    const {
      person_name
    } = params;

    // 构建 WHERE 条件
    let conditions = ['p.is_deleted = 0'];
    const queryParams = [];

    if (person_name) {
      conditions.push('p.person_name LIKE ?');
      queryParams.push(`%${person_name}%`);
    }

    const sql = `
      SELECT 
        p.*,
        d.department_name 
      FROM 
        tb_person p
        LEFT JOIN tb_department d ON p.department_id = d.department_id AND d.is_deleted = 0
      WHERE 
        ${conditions.join(' AND ')}
      ORDER BY 
        p.person_id DESC
    `;

    return await this.app.mysql.query(sql, queryParams);
  }

  /**
   * 获取所有人员列表 树状 无需分页
   * @returns {Promise<Array>} 人员列表
   */
  async findAllWithoutPagingTree(params = {}) {
    const sql1 = `
      SELECT 
        *
      FROM 
        tb_person
      WHERE 
        is_deleted = 0
    `;

    const personList = await this.app.mysql.query(sql1)

    const sql2 = `
      SELECT 
        *
      FROM 
        tb_department
      WHERE 
        is_deleted = 0
    `;

    const departmentList = await this.app.mysql.query(sql2)

    const result = []

    departmentList.map(departmentItem => {
      const treeObj = {}
      treeObj.title = departmentItem.department_name
      treeObj.value = `department-${departmentItem.department_id}`
      treeObj.key = `department-${departmentItem.department_id}`
      treeObj.selectable = false
      treeObj.children = []
      personList.map(personItem => {
        if (personItem.department_id === departmentItem.department_id) {
          treeObj.children.push({
            title: personItem.person_name,
            value: personItem.person_id,
            key: personItem.person_id,
          })
        }
      })
      result.push(treeObj)
    })


    return result;
  }
}

module.exports = PersonService;